package com.DAO.Impl;

import com.DAO.guardiansDAO;
import com.DBUtil.DBUtil;
import com.entity.Class;
import com.entity.Student;
import com.entity.guardians;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author 20x
 * @date 2024 - 12 -10
 **/
public class guardiansDAOImpl implements guardiansDAO {
    @Override
    public List<guardians> getAll(String where,int page,int size,String sortColumn,String sortOrder) {
        List<guardians> list = new ArrayList<>();
        String sql = "select * from guardians where 1=1 ";
        if(!where.equals("")){
            sql += " and  (name = '"+where+"' or phone = '"+where+"')" ;
        }
        if(sortColumn.equals("")){
            sortColumn = "guardiansId";
        }
        if(sortOrder.equals("")){
            sortOrder = "asc";
        }
        sql +="ORDER BY "+sortColumn+" "+sortOrder+" OFFSET "+(page-1)*size+" ROWS FETCH NEXT "+size+" ROWS ONLY";

        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            System.out.println(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                String guardiansId = rst.getString(1);
                String Gindex= rst.getString(2);
                String name2= rst.getString(3);
                String sex= rst.getString(4);
                String age= rst.getString(5);
                String phone2= rst.getString(6);
                String avatar= rst.getString(7);
                String spend= rst.getString(8);
                String pervLogin= rst.getString(9);
                String regDate= rst.getString(10);
                guardians guardians = new guardians(guardiansId,Gindex,name2,sex,age,phone2,avatar,spend,pervLogin,regDate);
                list.add(guardians);
            }
            DBUtil.close(rst,pst,conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public int count(String where) {
        int count = 0;
        String sql = "select count(*) from guardians where 1=1 ";
        if(!where.equals("")){
            sql += " and  (name = '"+where+"' or phone = '"+where+"')";
        }
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            System.out.println(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                count = rst.getInt(1);
            }
            DBUtil.close(rst,pst,conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }

    @Override
    public int add(guardians guardians) {
        int count = 0;
        String newID = new guardiansDAOImpl().newID();
        String sql = "INSERT INTO guardians (guardiansId, name, sex, age, phone,avatar,spend,pervLogin,regDate) VALUES\n" +
                "(?, ?, ?, ?, ? , ? ,'0','暂未登录',?)";

        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,newID);
            pst.setString(2,guardians.getName());
            pst.setString(3,guardians.getSex());
            pst.setString(4,guardians.getAge());
            pst.setString(5,guardians.getPhone());
            pst.setString(6,guardians.getAvatar());
            pst.setString(7,LocalDate.now().toString());
            count = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }

    @Override
    public String newID() {
        String newID = null;
        String oldId = null;
        String sql = "select max(guardiansId) from guardians";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            ResultSet rst = pst.executeQuery();
            while (rst.next()){
                oldId = rst.getString(1);
            }
            int oldLen =  oldId.length();
            newID = Integer.parseInt(oldId.substring(1,oldLen))+1+"";
            DBUtil.close(rst,pst,conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return "G"+newID;
    }

    @Override
    public int SendMessage(String recipientId, String recipientName, String title, String content, String image) {
        int count = 0;
        String sql = "insert into Info(PeopleId,InfoTitle,InfoContent,InfoImage,InfoDate) VALUES(?, ?, ?, ?, ?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,recipientId);
            pst.setString(2,title);
            pst.setString(3,content);
            pst.setString(4,image);
            pst.setString(5,LocalDate.now().toString());
            count = pst.executeUpdate();
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return count;
    }

    @Override
    public guardians selectGuardian(String id) {
        guardians guardians = null;
        String sql = "select * from guardians where guardiansId = ?";
        ResultSet rs = null;
        PreparedStatement pst = null;
        Connection conn = DBUtil.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,id);
            rs = pst.executeQuery();
            while (rs.next()){
                guardians = new guardians(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10));
            }
            DBUtil.close(null,pst,conn);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return guardians;
    }

    @Override
    public List<Student> getStudents(String guardianId) {
        List<Student> List = null;
        String sql = "select * from student where guardiansId =?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1,guardianId);
            rst = pst.executeQuery();
            List = new ArrayList<>();
            while (rst.next()){
                Student student = new Student(rst.getString(1),rst.getString(2),rst.getString(3),rst.getString(4),rst.getString(5),rst.getString(6),rst.getString(7));
                List.add(student);
            }
            DBUtil.close(null,pst,conn);
        }
        catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return List;
    }

    public static void main(String[] args) {
        guardiansDAO guardiansDAO = new guardiansDAOImpl();
//        List<guardians> guardians = guardiansDAO.getAll("", 1,5);
//        System.out.println(guardians);

//        int count = guardiansDAO.count("");
//        System.out.println(count);

//        String newID = guardiansDAO.newID();
//        System.out.println(newID);

//        guardians guardians = new guardians("80岁老太带俩娃", "80", "女", "18606638031", "../img/bg/default-avatar.jpg");
//        int count = guardiansDAO.add(guardians);

       List<Student> students = guardiansDAO.getStudents("G101");
        System.out.println(students);
    }
}
